﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class DonHangDAO
    {
        public string LayMaDHLonNhat() 
        {
            string sql = "SELECT TOP  1 MaDH FROM DONHANG ORDER BY MaDH DESC";
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader(); 
            reader.Read();
             string MaDH = reader.GetString(0);
            
            conn.Close();
            return MaDH;
        }

        public string NextID(string lastID, string prefixID)
        {
            if (lastID == "")
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string TaoMaDHMoi()
        {
            string LastID = LayMaDHLonNhat();
            string NewID = NextID(LastID, "DH");
            return NewID;
        }
       
        public int ThemDonHang(DonHangDTO dhdto)
        {            
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("proc_ThemDonHang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@madh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.MaDH;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@makh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.MaKH;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@ngaydh", SqlDbType.DateTime);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.NgayDH;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@matt", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.MaTT;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@maNVDH", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.NVQL;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@tongtien", SqlDbType.Float);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.TongTien;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;

        }

        public int HuyDonHang(DonHangDTO dhdto)
        {
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("usp_HuyDSDH_KH", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@madh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = dhdto.MaDH;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;

        }
     public List<DonHangDTO> XemDonHang(String bien,String MaKH, ref int soluong)
        {
            List<DonHangDTO> ds = new List<DonHangDTO>();
            SqlTransaction tran;
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = conn.CreateCommand(); //tao doi tuong command
            tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, " ");//start tran tren connection nay
           // tran = conn.BeginTransaction(IsolationLevel.Serializable, " ");
            cmd.Connection = conn;
            cmd.Transaction = tran;
            if (bien == "KH")           
                cmd.CommandText = "select count(MaKH) from DONHANG Where MaKH=@ma";

            else if (bien == "NV")
                cmd.CommandText = "select count(MaKH) from DONHANG Where MaNVDH=@ma";
               
            else if (bien == "ALL")
               cmd.CommandText = "select count(MaKH) from DONHANG";

                SqlParameter pa = new SqlParameter("@ma", SqlDbType.VarChar, 6);
                pa.Value = MaKH;
                cmd.Parameters.Add(pa);
                
            int sl = (int)cmd.ExecuteScalar();//gia tri output số lượng
            soluong = sl;

            System.Threading.Thread.Sleep(8000);

            cmd.Parameters.Clear();
     //       rd.Dispose();
            cmd.Dispose();
            // liệt kê đơn hàng
            if (bien == "KH")
                cmd.CommandText = "select * from DONHANG Where MaKH=@ma";

            else if (bien == "NV")
                cmd.CommandText = "select * from DONHANG Where MaNVDH=@ma";

            else if (bien == "ALL")
                cmd.CommandText = "select * from DONHANG";
            cmd.Parameters.Add(pa);
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DonHangDTO dto = new DonHangDTO();
                dto.MaDH = rd[0].ToString();
                dto.MaKH = rd[1].ToString();
                dto.NgayDH = DateTime.Parse(rd[2].ToString());
                dto.MaTT = int.Parse(rd[3].ToString());
                dto.NVQL = rd[4].ToString();
                dto.TongTien =float.Parse( rd[5].ToString());

                ds.Add(dto);
            }
            rd.Dispose();
            tran.Commit();
            cmd.Dispose();
            conn.Close();
            return ds;
        }
    
    }
}
